﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sofire.Data;
using Sofire.Data.XSql;
using System.Data;
using Sofire.Expressions;
using System.Xml;
using System.IO;
using System.Diagnostics;

namespace DataCommTest.XSqlTest
{
    public class XSqlTester : ITest, IModelLoadable
    {
        #region ITest 成员
        /*
         * CREATE TABLE DEMO_STUDENT
(
       STUDENT_ID NUMBER(18) NOT NULL PRIMARY KEY,
       STUDENT_NAME NVARCHAR2(10) NOT NULL,
       STUDENT_SEX NUMBER(1),
       STUDENT_BIRTHDAY DATE,
       STUDENT_PHOTO VARCHAR(100)
);
CREATE SEQUENCE SEQ_DEMO_STUDENT_ID;
         * 
INSERT INTO DEMO_STUDENT VALUES(SEQ_DEMO_STUDENT_ID.NEXTVAL,'张三',1,SYSDATE,'12345678901');
         */
        private static int index = 0;
        private static string FormatXml(string sUnformattedXml)
        {
            XmlDocument xd = new XmlDocument();
            xd.LoadXml(sUnformattedXml);
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            XmlTextWriter xtw = null;
            try
            {
                xtw = new XmlTextWriter(sw);
                xtw.Formatting = Formatting.Indented;
                xd.WriteTo(xtw);
            }
            finally
            {
                if(xtw != null)
                    xtw.Close();
            }
            return sb.ToString();
        }
        void Test(string name, XSqlParameterCollection parameters)
        {
            Console.WriteLine("======> {0} XML <======[{1}]", name, ++index);
            var xsql = Document[name];


            Console.WriteLine(FormatXml(xsql.Xml));
            Console.WriteLine("\r\n======> {0} SQL <======[{1}]", name, index);
            var command = Document.CreateCommand(xsql, parameters);
            Console.WriteLine(command);
            Console.WriteLine("CommandLength :{0}", command.CommandText.Length);
            Console.WriteLine("回车后继续...");
            Console.ReadLine();
            Console.Clear();
        }

        IXSqlDocument Document;

        private void hashCode_test()
        {
            XSqlParameterCollection coll1 = new XSqlParameterCollection();
            coll1.Add("studentID", 1);
            var code1 = System.Runtime.CompilerServices.RuntimeHelpers.GetHashCode(coll1);
            XSqlParameterCollection coll2 = new XSqlParameterCollection();
            coll2.Add("studentID", 1);

            var code2 = System.Runtime.CompilerServices.RuntimeHelpers.GetHashCode(coll2);
            Console.WriteLine(code1 == code2);
        }
        private void selectAllStudentByID_test1()
        {
            XSqlParameterCollection coll = new XSqlParameterCollection();
            coll.Add("studentID", 1);
            this.Test("selectAllStudentByID", coll);
        }
        private void selectAllStudentByID_test2()
        {
            XSqlParameterCollection coll = new XSqlParameterCollection();
            // coll.Add("studentID", null);
            this.Test("selectAllStudentByID", coll);
        }
        private void if_test()
        {
            XSqlParameterCollection coll = new XSqlParameterCollection();
            coll.Add("studentID", 1);
            this.Test("test_if", coll);
        }
        private void if_else_test1()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentID", 2);
            this.Test("test_if", coll);
        }
        private void if_else_test2()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentID", 2);
            coll.Add("studentName", "李四");
            this.Test("test_if", coll);
        }
        private void switch_test1()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentName", "张三");
            this.Test("test_switch", coll);
        }
        private void switch_test2()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentName", "李四");
            this.Test("test_switch", coll);
        }
        private void switch_test3()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentName", "王五");
            this.Test("test_switch", coll);
        }
        private void switch_test4()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentName", null);
            this.Test("test_switch", coll);
        }
        private void switch_test5()
        {
            this.Test("test_switch", null);
        }

        private void foreach_test1()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentSex", 1);
            coll.AddArray("studentNames", "张三", "李四", "王五");
            this.Test("test_foreach", coll);
        }
        private void foreach_test2()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentSex", 1);
            coll.AddArray("studentNames", "张三1", "李四", "王五");
            this.Test("test_foreach", coll);
        }
        private void foreach_test3()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("studentSex", 1);
            coll.AddArray("studentNames", "张三1", "王五");
            this.Test("test_foreach", coll);
        }

        private void function_test()
        {
            var coll = new XSqlParameterCollection();
            coll.Add("tableName", "demo1_FirstCaseValue");
            this.Test("function_test", coll);

            coll = new XSqlParameterCollection();
            coll.Add("tableName", "demo2_SecondCaseValue");
            this.Test("function_test", coll);

            coll = new XSqlParameterCollection();
            coll.Add("tableName", "demo3_DefaultCaseValue");
            this.Test("function_test", coll);
        }

        public class Assert
        {
            public static void AreEqual(object value1, object value2)
            {
                Console.WriteLine(object.Equals(value1, value2));
            }
        }
        public void Test()
        {
            XSqlFactory x = new XSqlFactory(Share.Engine);
            var document = x.Create
            (
                x.XSql
                ("test", XSqlCacheMode.None, null
                    , x.Text("SELECT * FROM Students")
                    , x.Trim
                        ("WHERE", "AND|OR"
                            , x.If("studentID=1", x.Text("AND studentID=0"))
                            , x.Else(x.Text("AND studentName=#{studentid}"))
                            , x.If("studentName!=null", x.Text("AND studentName=#{studentName}"))
                            , x.Else(x.Text("AND studentName IS NULL"))
                            , x.If
                                ("len(classes)>0"
                                    , x.Text("AND studentClass IN(")
                                    , x.Foreach
                                        ("class", "classes", null, ","
                                            , x.Text(",#{class}")
                                        )
                                    , x.Text(")")
                                )
                        )
                )
            );
            Document = document;
            Console.WriteLine(document.CreateXml());

            Console.ReadLine();
            this.Test("test", new XSqlParameterCollection()
                                .Add("studentID", "1"));


            this.Test("test", new XSqlParameterCollection()
                                .Add("studentID", "1")
                                .Add("studentName", "张三"));


            this.Test("test", new XSqlParameterCollection()
                                .Add("studentID", "1")
                                .Add("studentName", "张三")
                                .AddArray("classes", 1, 2, 3, 4, 5, 6));

        }
        public void Test2()
        {
            const string XSqlPath = "Student.xml";
            QueryEngineBase engine = new OracleQuery("ORCL", "fz_bpm", "fz_bpm");


            XSqlFactory x = new XSqlFactory(engine);

            var document = x
                .Create
                (
                    x.XSql
                    ("test", XSqlCacheMode.None, null
                        , x.Text("SELECT * FROM Students")
                        , x.Trim
                            ("WHERE", "AND|OR"
                                , x.If("studentID=1", x.Text("AND studentName='张三'"))
                                , x.Else(x.Text("AND studentName='李四'"))
                                , x.If("studentName!=null", x.Text("AND studentName=#{studentName}"))
                                , x.Else(x.Text("AND studentName IS NOT NULL"))
                            )
                    )
                );
            document.Save("test.xsql");

            Document = XSqlManager.Register<XSqlTester>(engine, "test.xsql");

            XSqlParameterCollection coll = new XSqlParameterCollection();
            coll.Add("studentID", 1);
            this.Test("test", coll);

            Console.ReadLine();
            XSqlManager.Remove<XSqlTester>();
            Document = XSqlManager.Register<XSqlTester>(engine, XSqlPath);
            //Console.WriteLine(Document.CreateXml());
            //Console.ReadLine();
            this.selectAllStudentByID_test1();
            this.selectAllStudentByID_test2();
            this.if_test();
            this.if_else_test1();
            this.if_else_test2();

            this.switch_test1();
            this.switch_test2();
            this.switch_test3();
            this.switch_test4();
            this.switch_test5();
            this.foreach_test1();
            this.foreach_test2();
            this.foreach_test3();
            this.function_test();
        }

        #endregion

        #region IModelLoadable 成员

        bool IModelLoadable.Load(IDataReader reader)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
